package com.vxdata.utils.modules.excel.easypoi;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
import com.google.common.collect.Lists;
import com.vxdata.common.core.domain.AjaxResult;
import com.vxdata.common.core.domain.entity.SysDept;
import com.vxdata.common.core.domain.entity.SysDictData;
import com.vxdata.system.service.ISysConfigService;
import com.vxdata.system.service.ISysDeptService;
import com.vxdata.system.service.ISysDictTypeService;
import com.vxdata.utils.modules.excel.easypoi.vo.ActivityQueryVo;
import com.vxdata.utils.modules.excel.easypoi.vo.AllCountVo;
import com.vxdata.utils.modules.excel.easypoi.vo.Constants;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.time.LocalDate;
import java.util.*;
import java.util.stream.Collectors;

/**
 * <!--easypoi Excel模板填充导出-->
 * <dependency>
 * <groupId>cn.afterturn</groupId>
 * <artifactId>easypoi-spring-boot-starter</artifactId>
 * <version>4.1.0</version>
 * </dependency>
 */
public class EasyPoiTemplateUtil {

    @Autowired
    private ISysDeptService deptService;
    @Autowired
    private ISysDictTypeService sysDictTypeService;

    @Autowired
    private ISysConfigService configService;


    @Value("${excel.templateUrl}")
    private String templateUrl;

    @Value("${excel.activityTemplateUrl}")
    private String activityTemplateUrl;

    @Value("${excel.libraryTemplateUrl}")
    private String libraryTemplateUrl;

    @Value("${excel.culturalCenterTemplateUrl}")
    private String culturalCenterTemplateUrl;

    @Value("${excel.culturalStationTemplateUrl}")
    private String culturalStationTemplateUrl;

    @Value("${excel.artisticTemplateUrl}")
    private String artisticTemplateUrl;

    @Value("${excel.museumTemplateUrl}")
    private String museumTemplateUrl;

    @Value("${excel.artTemplateUrl}")
    private String artTemplateUrl;


    private String outUrl = "D:\\Excel\\789.xlsx";

    /**
     * 前端下载服务人次统计表Excel
     *
     * @param response
     * @param activityQueryVo
     */

    public void export(HttpServletResponse response, ActivityQueryVo activityQueryVo) {
        try {
            activityQueryVo.setVerify("1");
            Map<String, Object> data = getData(activityQueryVo);
            Workbook workbook = export(templateUrl, data);
            String fileName = LocalDate.now().getYear() + "年人均接受文化场馆服务人次统计表";
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName + ".xlsx", "UTF-8"));
            workbook.write(response.getOutputStream());
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 本地生成Excel
     *
     * @param activityQueryVo
     * @return
     */

    public AjaxResult export(ActivityQueryVo activityQueryVo) {
        Map<String, Object> data = getData(activityQueryVo);
        try {
            Workbook workbook = export(templateUrl, data);
            FileOutputStream outputStream = new FileOutputStream(new File(outUrl));
            workbook.write(outputStream);
            outputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return AjaxResult.success(data);
    }

    /**
     * 模板数据替换
     *
     * @param templateUtl 模板路径
     * @param map         数据集合
     * @return
     * @throws IOException
     */
    private Workbook export(String templateUtl, Map<String, Object> map) throws IOException {
        File file = new File(templateUtl);
        String canonicalPath = file.getCanonicalPath();
        // 默认为false, 设置sheetName为true, 开启多sheet，
        TemplateExportParams params = new TemplateExportParams(canonicalPath, true);
        Workbook workbook = ExcelExportUtil.exportExcel(params, map);
        return workbook;
    }

    private Workbook exportTwoVenueExcel(String templateUtl, Map<String, Object> map, String sheetName) throws IOException {
        File file = new File(templateUtl);
        String canonicalPath = file.getCanonicalPath();
        // 默认为false, 设置sheetName为true, 开启多sheet，
        TemplateExportParams params = new TemplateExportParams(canonicalPath, false, sheetName);
        Workbook workbook = ExcelExportUtil.exportExcel(params, map);
        return workbook;
    }


    /**
     * 获取Excel表格统计数据
     *
     * @param activityQueryVo
     * @return
     */
    public Map<String, Object> getData(ActivityQueryVo activityQueryVo) {
        Map<String, Object> allMap = new HashMap<String, Object>();

        /** 获取 1汇总线下总服务人次 */
        List<Map<String, Object>> list0 = Lists.newArrayList();
        Map<String, Object> map = new HashMap<String, Object>();
        // 总计
        double allCount = 0;

        /** 获取 2图书馆 总服务人次 */
        activityQueryVo.setDeptId(Long.valueOf(Constants.VENUE_CATEGORY_LIBRARY));
        List<Map<String, Object>> library = getOneData(activityQueryVo);
        allMap.put("library", library);
        double libraryCount = (double) library.get(library.size() - 1).get("count");
        map.put(Constants.VENUE_CATEGORY_LIBRARY, libraryCount);
        allCount += libraryCount;

        /** 获取 3文化馆 总服务人次 */
        activityQueryVo.setDeptId(Long.valueOf(Constants.VENUE_CATEGORY_VENUE));
        List<Map<String, Object>> station = getOneData(activityQueryVo);
        allMap.put("station", station);
        double stationCount = (double) station.get(station.size() - 1).get("count");
        map.put(Constants.VENUE_CATEGORY_VENUE, stationCount);
        allCount += stationCount;


        /** 获取 4文化站 总服务人次 */
        activityQueryVo.setDeptId(Long.valueOf(Constants.VENUE_CATEGORY_STATION));
        List<Map<String, Object>> venue = getOneData(activityQueryVo);
        allMap.put("venue", venue);
        double venueCount = (double) venue.get(venue.size() - 1).get("count");
        map.put(Constants.VENUE_CATEGORY_STATION, venueCount);
        allCount += venueCount;

        /** 获取 5艺术表演场馆 总服务人次 */
        activityQueryVo.setDeptId(Long.valueOf(Constants.VENUE_CATEGORY_PERFORM));
        List<Map<String, Object>> perform = getOneData(activityQueryVo);
        allMap.put("perform", perform);
        double performCount = (double) perform.get(perform.size() - 1).get("count");
        map.put(Constants.VENUE_CATEGORY_PERFORM, performCount);
        allCount += performCount;

        /** 获取 6博物馆 总服务人次 */
        activityQueryVo.setDeptId(Long.valueOf(Constants.VENUE_CATEGORY_HISTORY));
        List<Map<String, Object>> history = getOneData(activityQueryVo);
        allMap.put("history", history);
        double historyCount = (double) history.get(history.size() - 1).get("count");
        map.put(Constants.VENUE_CATEGORY_HISTORY, historyCount);
        allCount += historyCount;

        /** 获取 7美术馆 总服务人次 */
        activityQueryVo.setDeptId(Long.valueOf(Constants.VENUE_CATEGORY_ART));
        List<Map<String, Object>> art = getOneData(activityQueryVo);
        allMap.put("art", art);
        double artCount = (double) art.get(art.size() - 1).get("count");
        map.put(Constants.VENUE_CATEGORY_ART, artCount);
        allCount += artCount;

        /** 获取 181综合 总服务人次 */
        activityQueryVo.setDeptId(Long.valueOf(Constants.VENUE_CATEGORY_COMPLEX));
        List<Map<String, Object>> complex = getOneData(activityQueryVo);
        allMap.put("complex", complex);
        double complexCount = (double) complex.get(complex.size() - 1).get("count");
        map.put(Constants.VENUE_CATEGORY_COMPLEX, complexCount);
        allCount += complexCount;

        /** 获取 182功能区 总服务人次 */
        activityQueryVo.setDeptId(Long.valueOf(Constants.VENUE_CATEGORY_FUNCTIONALITY));
        List<Map<String, Object>> functionality = getOneData(activityQueryVo);
        allMap.put("functionality", functionality);
        double functionalityCount = (double) functionality.get(functionality.size() - 1).get("count");
        map.put(Constants.VENUE_CATEGORY_FUNCTIONALITY, functionalityCount);
        allCount += functionalityCount;

        /** 获取 8其他 总服务人次 */
        activityQueryVo.setDeptId(Long.valueOf(Constants.VENUE_CATEGORY_OTHER));
        List<Map<String, Object>> other = getOneData(activityQueryVo);
        allMap.put("other", other);
        double otherCount = (double) other.get(other.size() - 1).get("count");
        map.put(Constants.VENUE_CATEGORY_OTHER, otherCount);
        allCount += otherCount;


        DecimalFormat df = new DecimalFormat("0.0000");

        //前端传输的统计年份
        Integer CountYear = activityQueryVo.getEndTime().getYear();
        //系统年份
        Integer NowYear = Calendar.getInstance().get(Calendar.YEAR);

        map.put("count", df.format(allCount));
        String population = "12.5";
        // 同期常住平均人口(万人)
        map.put("population", population);
        String format = df.format((float) allCount / Double.parseDouble(population));
        map.put("perCapita", format);

        list0.add(map);
        allMap.put("allList", list0);
        // 年份
        StringBuilder builder = new StringBuilder();
        builder.append(activityQueryVo.getStartTime().getYear());
        builder.append("年");
        if (activityQueryVo.getStartTime().getMonthValue() != activityQueryVo.getEndTime().getMonthValue()) {
            builder.append(activityQueryVo.getStartTime().getMonthValue());
            builder.append("月-");
            builder.append(activityQueryVo.getEndTime().getYear());
            builder.append("年");
            builder.append(activityQueryVo.getEndTime().getMonthValue());
            builder.append("月");
        } else {
            builder.append(activityQueryVo.getStartTime().getMonthValue());
            builder.append("月");
        }
        allMap.put("year", builder.toString());

        return allMap;
    }

    /**
     * 获取某场馆的统计数据
     *
     * @param activityQueryVo
     * @return
     */
    public List<Map<String, Object>> getOneData(ActivityQueryVo activityQueryVo) {

        List<Map<String, Object>> list = Lists.newArrayList();
        // 查询数据库统计结果
//        List<AllCountVo> library = activityMapper.countOneExcel(activityQueryVo);
        List<AllCountVo> library = new ArrayList<>();
        // 按照部门进行分类
        Map<Long, List<AllCountVo>> deptCollect = library.stream().collect(Collectors.groupingBy(AllCountVo::getDeptId));
        SysDept dept = new SysDept();
        dept.setParentId(activityQueryVo.getDeptId());
        // 统计有多少个部门
//        List<SysDept> sysDeptList = deptService.deptList(dept);
        List<SysDept> sysDeptList = deptService.selectDeptList(dept);

        // 获取字典
//        String activityTypeDict = activityService.getActivityTypeDict(String.valueOf(activityQueryVo.getDeptId()));
        List<SysDictData> sysDictDataList = sysDictTypeService.selectDictDataByType("activityTypeDict");


        int num = 1;
        for (SysDept sysDept : sysDeptList) {
            HashMap<String, Object> map = new HashMap<>();
            // 一级文化场馆名称
            map.put("deptName", sysDept.getDeptName());
            // 一级文化场馆下面的二级文化场馆数量
            int childrenNum = deptService.selectNormalChildrenDeptById(sysDept.getDeptId());
            map.put("childrenNum", childrenNum);
            // 遍历此部门各个活动的服务人次
            List<AllCountVo> collectList = deptCollect.get(sysDept.getDeptId());
            int count = 0;
            for (SysDictData sysDictData : sysDictDataList) {
                boolean flag = false;
                if (collectList != null && collectList.size() > 0) {
                    for (AllCountVo allCountVo : collectList) {
                        if (sysDictData.getDictValue().equals(allCountVo.getActivityType())) {
                            map.put(allCountVo.getActivityType(), txfloat(allCountVo.getPeopleNumber(), 10000));
                            count = count + allCountVo.getPeopleNumber();
                            flag = true;
                            break;
                        }
                    }
                }
//                if (!flag) {
//                    map.put(sysDictData.getDictValue(), 0);
//                }
            }
            // 合计
            map.put("count", txfloat(count, 10000));
            map.put("num", num);
            num++;
            list.add(map);
        }


        // 最下面一行的小计
        Map<String, List<AllCountVo>> activityList = library.stream().collect(Collectors.groupingBy(AllCountVo::getActivityType));

        HashMap<String, Object> map = new HashMap<>();
        map.put("deptName", "小计");

        int countAll = 0;
        for (SysDictData sysDictData : sysDictDataList) {
            List<AllCountVo> allCountVoList = activityList.get(sysDictData.getDictValue());
            if (allCountVoList != null && allCountVoList.size() > 0) {
                int count = 0;
                for (AllCountVo allCountVo : allCountVoList) {
                    count = count + allCountVo.getPeopleNumber();
                }
                countAll = countAll + count;
                map.put(sysDictData.getDictValue(), txfloat(count, 10000));
            } else {
                map.put(sysDictData.getDictValue(), 0);
            }
        }

        map.put("count", Double.parseDouble(txfloat(countAll, 10000)));
        map.put("num", num);


        list.add(map);
        return list;
    }

    /**
     * 除法运算，保留小数
     *
     * @param a 被除数
     * @param b 除数
     * @return 商
     * @author 袁忠明
     * @date 2018-4-17下午2:24:48
     */
    public static String txfloat(int a, int b) {
        //设置保留位数
        DecimalFormat df = new DecimalFormat("0.0000");
        return df.format((float) a / b);

    }

}
